﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using DapperDemo.Model;
using DapperDemo.Repository.Entities;
using DapperDemo.Repository.IRepo;

namespace DapperDemo.Repository.Repo;

public class CompanyRepo(Db context) : ICompanyRepo
{
	public async Task<IEnumerable<Company>> GetCompanies()
	{
		const string query = "SELECT Id, Name, Address, Country FROM Companies";

		using var connection = context.CreateConnection();
		var companies = await connection.QueryAsync<Company>(query);
		return companies.ToList();
	}

	public async Task<Company> GetCompany(int id)
	{
		const string query = "SELECT * FROM Companies WHERE Id = @Id";

		using var connection = context.CreateConnection();
		var company = await connection.QuerySingleOrDefaultAsync<Company>(query, new { id });

		return company;
	}

	public async Task<Company> CreateCompany(CompanyDto company)
	{
		const string query = "INSERT INTO Companies (Name, Address, Country) VALUES (@Name, @Address, @Country)" +
		                     "SELECT CAST(SCOPE_IDENTITY() as int)";

		var parameters = new DynamicParameters();
		parameters.Add("Name", company.Name, DbType.String);
		parameters.Add("Address", company.Address, DbType.String);
		parameters.Add("Country", company.Country, DbType.String);

		using var connection = context.CreateConnection();
		var id = await connection.QuerySingleAsync<int>(query, parameters);

		var createdCompany = new Company
		{
			Id = id,
			Name = company.Name,
			Address = company.Address,
			Country = company.Country
		};

		return createdCompany;
	}

	public async Task UpdateCompany(int id, CompanyDto company)
	{
		var query = "UPDATE Companies SET Name = @Name, Address = @Address, Country = @Country WHERE Id = @Id";

		var parameters = new DynamicParameters();
		parameters.Add("Id", id, DbType.Int32);
		parameters.Add("Name", company.Name, DbType.String);
		parameters.Add("Address", company.Address, DbType.String);
		parameters.Add("Country", company.Country, DbType.String);

		using (var connection = context.CreateConnection())
		{
			await connection.ExecuteAsync(query, parameters);
		}
	}

	public async Task DeleteCompany(int id)
	{
		var query = "DELETE FROM Companies WHERE Id = @Id";

		using var connection = context.CreateConnection();
		await connection.ExecuteAsync(query, new { id });
	}

	public async Task<Company> GetCompanyByEmployeeId(int id)
	{
		var procedureName = "ShowCompanyForProvidedEmployeeId";
		var parameters = new DynamicParameters();
		parameters.Add("Id", id, DbType.Int32, ParameterDirection.Input);

		using var connection = context.CreateConnection();
		var company = await connection.QueryFirstOrDefaultAsync<Company>
			(procedureName, parameters, commandType: CommandType.StoredProcedure);

		return company;
	}

	public async Task<Company> GetCompanyEmployeesMultipleResults(int id)
	{
		const string query = "SELECT * FROM Companies WHERE Id = @Id;" +
		                     "SELECT * FROM Employees WHERE CompanyId = @Id";

		using var connection = context.CreateConnection();
		using var multi = await connection.QueryMultipleAsync(query, new { id });
		var company = await multi.ReadSingleOrDefaultAsync<Company>();
		if (company != null)
			company.Employees = (await multi.ReadAsync<Employee>()).ToList();

		return company;
	}

	public async Task<List<Company>> GetCompaniesEmployeesMultipleMapping()
	{
		const string query = "SELECT * FROM Companies c JOIN Employees e ON c.Id = e.CompanyId";

		using var connection = context.CreateConnection();
		var companyDict = new Dictionary<int, Company>();

		var companies = await connection.QueryAsync<Company, Employee, Company>(
			query, (company, employee) =>
			{
				if (!companyDict.TryGetValue(company.Id, out var currentCompany))
				{
					currentCompany = company;
					companyDict.Add(currentCompany.Id, currentCompany);
				}

				currentCompany.Employees.Add(employee);
				return currentCompany;
			}
		);

		return companies.Distinct().ToList();
	}

	public async Task CreateMultipleCompanies(List<CompanyDto> companies)
	{
		const string query = "INSERT INTO Companies (Name, Address, Country) VALUES (@Name, @Address, @Country)";

		using var connection = context.CreateConnection();
		connection.Open();

		using var transaction = connection.BeginTransaction();
		foreach (var company in companies)
		{
			var parameters = new DynamicParameters();
			parameters.Add("Name", company.Name, DbType.String);
			parameters.Add("Address", company.Address, DbType.String);
			parameters.Add("Country", company.Country, DbType.String);

			await connection.ExecuteAsync(query, parameters, transaction: transaction);
			//throw new Exception();
		}

		transaction.Commit();
	}
}